-- This script demoes the issues with name resolution you can run into
-- with temp tables.
-- © Erland Sommarskog 2020.

USE SQLTitbits;
go
-- Temp table name misspelled, yet procedure is created.
CREATE OR ALTER PROCEDURE dbo.Bad_TempTable_1 AS
   CREATE TABLE #mytemptable(a int NOT NULL);
   SELECT a FROM #mytmptable;
go
-- Wrong column name, yet procedure is created.
CREATE OR ALTER PROCEDURE dbo.Bad_TempTable_2 AS
   CREATE TABLE #mytemptable(a int NOT NULL);
   SELECT b FROM #mytemptable;
go
-- Note that the alias for the Customers table is missing, 
-- yet the procedure is created successfully.
CREATE OR ALTER PROCEDURE dbo.Bad_TempTable_3 AS
   CREATE TABLE #mytemptable(a int NOT NULL);
   SELECT C.CustomerID, C.CustomerName, C.City
   FROM   dbo.Customers
   JOIN   #mytemptable m ON C.CustomerID = m.a;
go
-- All these three executions fails - with the errors we might have 
-- expected when we created the procedure.
EXEC dbo.Bad_TempTable_1;
go
EXEC dbo.Bad_TempTable_2;
go
EXEC dbo.Bad_TempTable_3;
go

-- The same errors with a table variable instead. All procedures fails to
-- create because of the errors.
CREATE OR ALTER PROCEDURE dbo.Bad_TableVar_1 AS
   DECLARE @mytemptable TABLE(a int NOT NULL);
   SELECT a FROM @mytmptable;
go
CREATE OR ALTER PROCEDURE dbo.Bad_TableVar_2 AS
   DECLARE @mytemptable TABLE (a int NOT NULL);
   SELECT b FROM @mytemptable;
go
CREATE OR ALTER PROCEDURE dbo.Bad_TableVar_3 AS
   DECLARE @mytemptable TABLE (a int NOT NULL);
   SELECT C.CustomerID, C.CustomerName, C.City
   FROM   dbo.Customers
   JOIN   @mytemptable m ON C.CustomerID = m.a ;
go

-- Note that when #mytemptable exists when you attempt to create the procedure, 
-- the incorrect column name yields an error.
CREATE TABLE #mytemptable(a int NOT NULL);
go
CREATE OR ALTER PROCEDURE Bad_TempTable_2 AS;
   CREATE TABLE #mytemptable(a int NOT NULL)
   SELECT b FROM #mytemptable;
go

-- This is a very confusing scenario. Note that the two procedures creates
-- a temp table with the same name, but different definition.
CREATE OR ALTER PROCEDURE dbo.inner_sp AS
   CREATE TABLE #temp(a int NOT NULL, b int NOT NULL, c int NOT NULL);
   SELECT a, b, c FROM #temp;
go
-- Run this statement to place the plan for inner_sp in cache.
EXEC dbo.inner_sp;
go

CREATE OR ALTER PROCEDURE dbo.outer_sp AS
   CREATE TABLE #temp(a int NOT NULL, b int NOT NULL);
   SELECT a, b FROM #temp;
   EXEC dbo.inner_sp;
go
-- This call succeeds, and you see two result sets.
EXEC dbo.outer_sp;
go

-- Clear the plan cache. (Never run this command in production!)
DBCC FREEPROCCACHE;
go
-- Now this fails, because compilation of inner_sp is done with the outer
-- defintion of #temp. Moral: give your temp tables distinct names!
EXEC dbo.outer_sp;
